import os
import sys
from typing import List

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from src.tasks.enterprise.EntEnterprise import EntEnterprise
from src.utils import CommonUtils, BatchUtils
from src.utils.CommonUtils import getProperty


class SyncEnterpriseBaseInfo:
    def __init__(self, logger, localDb, dataCenterDb, fcdb):
        self.logger = logger
        self.localDb = localDb
        self.dataCenterDb = dataCenterDb
        self.fcdb = fcdb
        self.size = 1000
        self.regions = {}
        self.listed = set()
        self.cityInvestmentDatas = {}

    # 抽取待更新主体名单，classify_type = 0
    def __queryLocal__(self):
        sql = "select id, credit_code, comp_code, ent_type from ent_enterprise where classify_type=0"
        return self.localDb.Query(sql)
        # 抽取基础数据

    # 查询城投所属区域代码
    def __queryCityInvestmentData__(self):
        sql = """ SELECT c.credit_code,
                     max(case when d.second_code = 'CTJC002001' then  d.value ELSE null END ) 'CTJC002001',
                     max(case when d.second_code = 'CTJC002003' then  d.value ELSE null END) 'CTJC002003' ,
                     max(case when d.second_code = 'CTJC002005' then  d.value ELSE null END ) 'CTJC002005'
                     FROM city_investment_companies c
                     inner JOIN city_investment_companies_data_detail d ON c.id = d.foreign_id
                     INNER JOIN enterprise_info_custom ic ON ic.credit_code = c.credit_code
                     WHERE c.ver=0 AND c.release_status=1 AND c.`status`=1 
                     AND d.second_code IN ('CTJC002001','CTJC002003','CTJC002005')
                     AND ic.type_code='T002' AND ic.ver=0 AND ic.release_status =1 AND ic.audit_status = 1
                     GROUP BY c.credit_code
            """
        return self.dataCenterDb.Query(sql)

    # 缓存所有区域代码
    def __queryRegions__(self):
        sql = " SELECT * from regions WHERE ver=0 AND release_status=1"
        return self.dataCenterDb.Query(sql)

    def __queryAllFromDcDb__(self):
        sql = """
                SELECT c.credit_code, c.type_code, c.score_type, s.name AS industry_model,q.regist_capi, q.scope, custom_nature_code
                FROM enterprise_info_v2 c
                INNER JOIN enterprise_used_for u ON c.credit_code = u.credit_code
                LEFT JOIN enterprise_type_score s ON s.value = c.score_type
                LEFT JOIN qcc_enterprise_info q ON q.credit_code = c.credit_code 
                WHERE  
                u.scenario_id = 2 AND u.used = 1        
                """
        return self.dataCenterDb.Query(sql)

    # 从财汇抽取行业数据
    def __queryFromFcdb__(self, compCodes):
        sql = """select c.COMPCODE, i2.LEVEL1CODE AS SW_L1_CODE, i2.LEVEL2CODE AS SW_L2_CODE,
                c.REGION,c.ORGTYPE,c.REGCAPITAL,c.FOUNDDATE,c.REGADDR,c.BIZSCOPE
                from TQ_COMP_INFO c
                left join TQ_COMP_INDUSTRY i2 on c.COMPCODE  = i2.COMPCODE  and i2.INDCLASSCODE='2222' AND i2.ENDDATE='19000101'
                where c.COMPCODE in %s
              """
        return self.fcdb.Query(sql, (compCodes,))

    # 从财汇抽取股东数据
    def __queryShareholder__(self, compCodes):
        sql = """
                  select * from (
                  select ITCODE as COMPCODE, CR0002_003  as SHCOMPNAME, CR0002_006 as PERCENT, 
                  row_number()  over ( partition by ITCODE order by CR0002_004  DESC ) as no
                  from TCR0002_V2_1 where ITCODE in %s  and FLAG  in (0, 2)
                  limit 10000
                  ) t where t.no = 1
              """
        return self.fcdb.Query(sql, (compCodes,))

    # 从财汇抽取实际控制人
    def __queryActualController__(self, compCodes):
        sql = """ 
                  SELECT SITCode, ITNAME_P FROM (
                  SELECT  SITCode, ITNAME_P, ROW_NUMBER() OVER ( PARTITION BY SITCode ORDER BY CR0071_001 DESC  ) AS NO
                  FROM TCR0071_V2
                  WHERE FLAG IN (0,2) AND SITCode IS NOT null
                  AND SITCode in %s
                  limit 10000
                  ) t WHERE t.no = 1        
              """
        return self.fcdb.Query(sql, (compCodes,))

    def __queryListed__(self):
        sql = """ 
                SELECT DISTINCT COMPCODE
                FROM tq_sk_basicinfo
                WHERE
                    SETYPE IN ('101','102','103','104')
                    AND EXCHANGE IN ('001002','001003','001066') 
                    AND LISTSTATUS=1
                    AND ISVALID=1
                """
        return self.fcdb.Query(sql)

    # 写库
    def __writedb__(self, updateDatas):
        sql = """
            REPLACE INTO ent_enterprise
            (id, enterprise_name, credit_code, ent_type, ent_type_sub, qe_eval, 
            foreign_external_eval_date, external_eval_date, external_eval, foreign_external_eval, nature, 
            debt_num, debt_scale, trade_type, srm_trade_type, srm_trade_type_superior, 
            sw_trade_type, sw_trade_type_superior, region_code, region_name, 
            platform_region_code, platform_region_name, external_eval_info, external_eval_organization, 
            foreign_external_eval_info, foreign_external_eval_organization, enterprise_control, share_holder, 
            share_per, position, update_time, create_time, comp_code, bank_region, ccx_industry_code, 
            qe_sort, qe_year, qe_state, qe_rating_date, administrative_id, external_sort, listed, delete_flag)
            VALUES
            (%(id)s,%(enterprise_name)s,%(credit_code)s,%(ent_type)s,%(ent_type_sub)s,%(qe_eval)s,
            %(foreign_external_eval_date)s,%(external_eval_date)s,%(external_eval)s,%(foreign_external_eval)s,%(nature)s,
            %(debt_num)s,%(debt_scale)s,%(trade_type)s,%(srm_trade_type)s,%(srm_trade_type_superior)s,
            %(sw_trade_type)s,%(sw_trade_type_superior)s,%(region_code)s,%(region_name)s,
            %(platform_region_code)s,%(platform_region_name)s,%(external_eval_info)s,%(external_eval_organization)s,
            %(foreign_external_eval_info)s,%(foreign_external_eval_organization)s,%(enterprise_control)s,%(share_holder)s,
            %(share_per)s,%(position)s,%(update_time)s,%(create_time)s,%(comp_code)s,%(bank_region)s,%(ccx_industry_code)s,
            %(qe_sort)s,%(qe_year)s,%(qe_state)s,%(qe_rating_date)s,%(administrative_id)s,%(external_sort)s,%(listed)s,%(delete_flag)s)
            """
        return self.localDb.ExecMany(sql, updateDatas)

    def __deleteByCodes__(self, deleteCodes):
        sql = """
            update ent_enterprise
            set
            enterprise_name = CONCAT(REPLACE(enterprise_name,'（失效）', ''), '（失效）'),
            delete_flag = 1
            where credit_code in %s
        """
        return self.localDb.ExecNoneQuery(sql, (deleteCodes,))

    def __queryModelIndustryMapping__(self):
        sql = "SELECT * FROM model_industry_mapping"
        return self.localDb.Query(sql)

    def __queryBankDatas__(self):
        sql = "SELECT credit_code,province_id,city_id,country_id,region_id FROM bank_enterprise WHERE ver= 0 AND release_status = 1 AND audit_status = 1"
        return self.dataCenterDb.Query(sql)

    def __getPosition__(self, creditCode):
        if creditCode not in self.cityInvestmentDatas:
            return None
        return self.cityInvestmentDatas[creditCode]["CTJC002003"]

    def __fillRegions__(self, ent: EntEnterprise):
        """
        拼装城投区域数据
        """
        regionData = None
        # 确定城投所属区域
        if ent.credit_code in self.cityInvestmentDatas.keys():
            cityInvestmentData = self.cityInvestmentDatas[ent.credit_code]
            ent.platform_region_code = cityInvestmentData["CTJC002001"]
            if ent.platform_region_code in self.regions.keys():
                regionData = self.regions[ent.platform_region_code]

        # 确定功能区，处理上级行政区域
        if regionData is not None:
            adminlevel = regionData['fun_parent_administrative_level']
            # 非功能区直接用所在行政区等级，功能区用按照规则采用省市区代码
            if adminlevel is None:
                ent.administrative_id = regionData["code"]
            elif adminlevel == 3:
                ent.administrative_id = regionData["county_code"]
            elif adminlevel == 2:
                ent.administrative_id = regionData["city_code"]
            elif adminlevel == 1:
                ent.administrative_id = regionData["province_code"]

    def __getTradeType__(self, entData, dcData):
        entType = entData.ent_type
        # 非产业企业，智象行业为固定值
        if entType is not None:
            if entType == "T002":
                return "城投"
            elif entType == "T003":
                return "商业银行"
            elif entType == "T004":
                return "证券公司"
            elif entType == "T006":
                return "担保公司"

        if entType == "T001":
            # 产业企业 智象行业名字部分修正
            tradeType = self.__getProperty__(dcData, "industry_model")
            if tradeType == '交通运输-其他交通运输':
                return '交通运输-其他'
            elif tradeType == '食品饮料':
                return '轻工制造-食品饮料'
            elif tradeType == '轻工制造-其他':
                return '轻工制造-其他制造业'
            else:
                return tradeType

        if entType == "T005":
            scoreType = dcData.get("score_type")
            if scoreType is not None:
                if scoreType == "HY040":
                    return "融资租赁"
                elif scoreType == "HY041":
                    return "人身保险"
                elif scoreType == "HY042":
                    return "财产保险"
                elif scoreType == "HY043":
                    return "资产管理"
                elif scoreType == "HY044":
                    return "消费金融"

    @staticmethod
    def __getProperty__(data, key):
        return getProperty(data, key)

    def __updateFcdbData__(self, ent: EntEnterprise, fcdbData: dict):

        # 申万一级行业
        ent.sw_trade_type_superior = self.__getProperty__(fcdbData, "SW_L1_CODE")

        # 申万二级行业
        ent.sw_trade_type = self.__getProperty__(fcdbData, "SW_L2_CODE")

        # 区域
        ent.region_code = self.__fixRegion__(self.__getProperty__(fcdbData, "REGION"))

        # 注册资本
        ent.registered_capital = self.__getProperty__(fcdbData, 'REGCAPITAL')

        # 成立时间
        ent.establishment_date = CommonUtils.strToDate(self.__getProperty__(fcdbData, 'FOUNDDATE'))

        # 注册地址
        ent.registered_address = self.__getProperty__(fcdbData, 'REGADDR')

        # 经营范围
        ent.business_scope = self.__getProperty__(fcdbData, 'BIZSCOPE')

    def __updateShareHolderData__(self, ent: EntEnterprise, shareHolderData: dict):
        # 第一大股东
        ent.share_holder = self.__getProperty__(shareHolderData, "SHCOMPNAME")

        # 占股比例
        ent.share_per = self.__getProperty__(shareHolderData, "PERCENT")

    def __updateActualControllerData__(self, ent: EntEnterprise, actualControllerData: dict):
        # 实际控制人
        ent.enterprise_control = self.__getProperty__(actualControllerData, "ITNAME_P")

    def __updateBankData__(self, ent: EntEnterprise, bankData: dict):
        # 银行区域
        ent.bank_region = self.__getProperty__(bankData, "region_id")

    @staticmethod
    def __fixRegion__(fcdbRegion: str):
        if fcdbRegion is None:
            return None
        if fcdbRegion.startswith("CN"):
            return fcdbRegion[2:]

    # 同步入口
    def update(self, enterpriseList: List[EntEnterprise]):
        self.logger.info("更新主体基础信息 开始")

        # 待更新名单
        self.logger.info("待更新主体：%d" % len(enterpriseList))

        # 加载上市企业
        self.logger.info("加载 加载上市企业")
        listed = self.__queryListed__()
        self.listed = set(d["COMPCODE"] for d in listed)
        self.logger.info("加载 加载上市企业：%d" % len(self.listed))
        for ent in enterpriseList:
            # 加上上市标志
            ent.listed = 0 if ent.comp_code not in self.listed else 1

        # 加载区域数据
        self.logger.info("加载 数据中心区域数据")
        regionsData = self.__queryRegions__()
        self.regions = {d["code"]: d for d in regionsData}
        self.logger.info("加载 数据中心区域数据：%d" % len(self.regions))

        # 加载城投数据
        self.logger.info("加载 数据中心城投数据")
        cityInvestmentDatas = self.__queryCityInvestmentData__()
        self.cityInvestmentDatas = {d["credit_code"]: d for d in cityInvestmentDatas}
        self.logger.info("加载 数据中心城投数据：%d" % len(self.cityInvestmentDatas))
        for ent in enterpriseList:
            # 所属区域（仅城投）， 上级行政区（仅功能区），实际作业区域
            self.__fillRegions__(ent)
            # 城投地位
            ent.position = self.__getPosition__(ent.credit_code)

        # 从财汇抽取企业基础数据
        self.logger.info("加载 财汇企业数据")
        compCodes = list(ent.comp_code for ent in enterpriseList)
        fcdbDatas = BatchUtils.batchQuery(self.__queryFromFcdb__, compCodes, self.size)
        fcdbDatas = {d["COMPCODE"]: d for d in fcdbDatas}
        self.logger.info("加载 汇企业数据： %d" % len(fcdbDatas))

        for ent in enterpriseList:
            fcdbData = self.__getProperty__(fcdbDatas, ent.comp_code)
            if fcdbData is not None:
                self.__updateFcdbData__(ent, fcdbData)

        # 从财汇抽取股东
        self.logger.info("加载 财汇股东信息")
        shareHolderDatas = BatchUtils.batchQuery(self.__queryShareholder__, compCodes, self.size)
        shareHolderDatas = {d["COMPCODE"]: d for d in shareHolderDatas}
        self.logger.info("加载 财汇股东信息：%d" % len(shareHolderDatas))

        for ent in enterpriseList:
            shareHolderData = self.__getProperty__(shareHolderDatas, ent.comp_code)
            if shareHolderData is not None:
                self.__updateShareHolderData__(ent, shareHolderData)

        # 从财汇抽取实际控制人
        self.logger.info("加载 财汇实际控制人信息")
        actualControllerDatas = BatchUtils.batchQuery(self.__queryActualController__, compCodes, self.size)
        actualControllerDatas = {d["SITCode"]: d for d in actualControllerDatas}
        self.logger.info("加载 财汇实际控制人信息：%d" % len(actualControllerDatas))

        for ent in enterpriseList:
            actualControllerData = self.__getProperty__(actualControllerDatas, ent.comp_code)
            self.__updateActualControllerData__(ent, actualControllerData)

        # 加载银行数据
        bankDatas = self.__queryBankDatas__()
        self.logger.info("加载 数据中心银行数据")
        bankDatas = {d["credit_code"]: d for d in bankDatas}
        self.logger.info("加载 数据中心银行数据：%d" % len(bankDatas))
        for ent in enterpriseList:
            bankData = self.__getProperty__(bankDatas, ent.credit_code)
            if bankData is not None:
                self.__updateBankData__(ent, bankData)

        self.logger.info("更新主体基础信息 完成")

    def writedb(self, enterpriseList: List[EntEnterprise], deleteCodes: List):
        self.logger.info("写入数据库 开始")

        # 主体分类缺失的，一律为 T005:其他
        for ent in enterpriseList:
            if ent.ent_type is None:
                ent.ent_type = "T005"

        updateDatas = list(ent.__dict__ for ent in enterpriseList)
        BatchUtils.batchUpdate(self.__writedb__, updateDatas, self.size)

        # 逻辑删除失效企业
        if len(deleteCodes) > 0:
            deleted = self.__deleteByCodes__(deleteCodes=deleteCodes)
            self.logger.info(f"逻辑删除无效企业 {deleted} 条")

        self.logger.info("写入数据库 完成")
